package com.oceansoft.mobile.vote.service;

import com.oceansoft.mobile.common.base.entity.Result;
import com.oceansoft.mobile.common.dao.ICommonDao;
import com.oceansoft.mobile.vote.domain.Tickets;
import com.oceansoft.mobile.vote.domain.VoteInfo;
import org.springframework.jdbc.core.simple.ParameterizedBeanPropertyRowMapper;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;
import java.util.List;

/**
 * Created by zhangxl on 2017/12/28.
 */

@Service
public class VoteServiceImpl implements IVoteService {

    @Resource
    private ICommonDao commonDao;

    @Override
    public int countVote(String openId) {
        String sql = " select count(1) from T_TP_VOTE where trunc(CREATETIME) = trunc(sysdate) AND USERID = ?";
        int count = commonDao.getCount(sql,openId);
        return count;
    }

    @Override
    public List<VoteInfo> findVoteInfos() {
        String sql= "SELECT T.dbid,t.bh,T.NAME,T.CONTENT,T.remark,NVL (s.tickets, 0) tickets ,rank () OVER (ORDER BY tickets desc nulls last) rank FROM T_TP_INFO T LEFT JOIN (" +
                " SELECT tt.INFOID,COUNT (1) tickets FROM T_TP_VOTE tt GROUP BY tt.INFOID ) s ON T.dbid = s.INFOID" +
                " WHERE T .status = 1 ORDER BY to_number(T.bh) ";
        return commonDao.loadList(sql, ParameterizedBeanPropertyRowMapper.newInstance(VoteInfo.class));
    }

    @Override
    public VoteInfo getVoteInfoById(String id) {
        String sql =
        "SELECT T.dbid,t.bh,T.NAME,T.CONTENT,T.remark,NVL (s.tickets, 0) tickets FROM T_TP_INFO T LEFT JOIN (" +
                " SELECT tt.INFOID,COUNT (1) tickets FROM T_TP_VOTE tt GROUP BY tt.INFOID ) s ON T.dbid = s.INFOID" +
                " WHERE T .status = 1 and t.dbid=? ORDER BY T.dbid ";

//                "select dbid,name,remark,content from T_TP_INFO where dbid= ?";
        return commonDao.loadOne(sql,ParameterizedBeanPropertyRowMapper.newInstance(VoteInfo.class),id);
    }

    @Override
    public void userVote(Tickets ticket) {
        commonDao.saveOne("INSERT INTO T_TP_VOTE(infoid,createtime,userid,ly) VALUES(:infoid,:createtime,:userid,:ly)", ticket);
    }

    @Override
    public String countAllTickets() {
        String sql="select count(1) tickets from T_TP_VOTE";
        int num =commonDao.getCount(sql);
        return num+"";
    }

    @Override
    public String countAllPersons() {
        String sql="select count(distinct userid) persons  from T_TP_VOTE ";
        int num = commonDao.getCount(sql);
        return num+"";
    }

    @Override
    public List<Tickets> findTicket() {
        String sql = "select * from(\n" +
                "select userid, count(t.userid) ticket\n" +
                "  from T_TP_VOTE t\n" +
                " where t.createtime >=\n" +
                "       to_date('2018-01-15 00:00:00', 'yyyy-mm-dd hh24:mi:ss')\n" +
                "   and t.createtime <=\n" +
                "       to_date('2018-01-15 23:59:59', 'yyyy-mm-dd hh24:mi:ss') \n" +
                " group by t.userid \n" +
                ")where ticket<3";

        return commonDao.loadList(sql,ParameterizedBeanPropertyRowMapper.newInstance(Tickets.class) );
    }

    @Override
    public List<Tickets> findDetail(String userid) {
        String sql = "select t.* from T_TP_VOTE t where t.userid=? and t.createtime >=\n" +
                "       to_date('2018-01-15 00:00:00', 'yyyy-mm-dd hh24:mi:ss')\n" +
                "   and t.createtime <=\n" +
                "       to_date('2018-01-15 23:59:59', 'yyyy-mm-dd hh24:mi:ss')  ";
        return commonDao.loadList(sql,ParameterizedBeanPropertyRowMapper.newInstance(Tickets.class),userid);
    }

    @Override
    public List<VoteInfo> getVoteInfoList() {
        String sql ="select t.* from T_TP_INFO t where T .status = 1 order by to_number(T.bh)";
        return commonDao.loadList(sql,ParameterizedBeanPropertyRowMapper.newInstance(VoteInfo.class) );
    }

    @Override
    public Result update(HttpServletRequest request) {
        String dbid = request.getParameter("id");
        String name = request.getParameter("name");
        String content = request.getParameter("content");
        String remark = request.getParameter("remark");
         int i = commonDao.updateStatusByPbId(name,content,dbid,remark);
        if(i>0){
            return new Result(Result.SUCC, "保存成功");
        }else {
            return new Result(Result.FAILURE, "保存失败");
        }


    }



}
